package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.RentalInfoDto;

public class RentalContractDaoImp extends ViewListDao implements RentalContractDao {
	
	protected RentalContractDaoImp() throws TmsDaoException {
		super();
	}
	protected RentalContractDaoImp(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	/**
	 * 插入合同信息
	 * */
	public void insertContractInfo(RentalInfoDto dto)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_CONTRACT_INFO);
			sql.append("(");		
			sql.append(" SERIAL, ");			
			sql.append(" RENT, ");
			sql.append(" STARTDATE, ");
			sql.append(" ENDDATE, ");
			sql.append(" OWNERNAME, ");
			sql.append(" OWNERSEX, ");
			sql.append(" OWNERBIRTHDAY, ");
			sql.append(" OWNERIDCARDNO, ");
			sql.append(" OWNERPHONE, ");			
			sql.append(" OWNERADDRESS, ");			
			sql.append(" REMARK, ");
			sql.append(" INSERTUSERID,");
			sql.append(" INSERTTIME, ");			
			sql.append(" UPDATEUSERID, ");
			sql.append(" UPDATETIME, ");
			sql.append(" PHOTO_1,");
			sql.append(" PHOTO_2,");
			sql.append(" PHOTO_3,");
			sql.append(" PHOTO_4,");
			sql.append(" PHOTO_5,");
			sql.append(" PHOTO_6,");
			sql.append(" PHOTO_7,");
			sql.append(" PHOTO_8,");
			sql.append(" STATUS,");
			sql.append(" LOCATIONSERIAL ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),?,SYSDATE(),?,?,?,?,?,?,?,?,?,? )");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getRent());
			ps.setTimestamp(3, dto.getStartDate());
			ps.setTimestamp(4, dto.getEndDate());
			ps.setString(5, dto.getOwnerName());
			ps.setInt(6, dto.getOwnerSex());
			ps.setTimestamp(7, dto.getOwnerBirthday());
			ps.setString(8, dto.getOwnerIdCardNo());
			ps.setString(9, dto.getOwnerPhone());
			ps.setString(10, dto.getOwnerAddress());
			ps.setString(11, dto.getRemark());
			ps.setInt(12, dto.getInsertUserId());
			ps.setInt(13, dto.getUpdateUserId());
			ps.setString(14, dto.getPhoto1());
			ps.setString(15, dto.getPhoto2());
			ps.setString(16, dto.getPhoto3());
			ps.setString(17, dto.getPhoto4());
			ps.setString(18, dto.getPhoto5());
			ps.setString(19, dto.getPhoto6());
			ps.setString(20, dto.getPhoto7());
			ps.setString(21, dto.getPhoto8());
			ps.setInt(22, dto.getStatus());
			ps.setString(23, dto.getLocationSerial());
			ps.executeUpdate();
					
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	/**
	 * 更新合同信息
	 * */
	public void updateContractInfo(RentalInfoDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_CONTRACT_INFO);
			sql.append(" SET ");			
			sql.append(" RENT=?, ");
			sql.append(" STARTDATE=?, ");
			sql.append(" ENDDATE=?, ");
			sql.append(" OWNERNAME=?, ");
			sql.append(" OWNERSEX=?, ");
			sql.append(" OWNERBIRTHDAY=?, ");
			sql.append(" OWNERIDCARDNO=?, ");
			sql.append(" OWNERPHONE=?, ");			
			sql.append(" OWNERADDRESS=?, ");			
			sql.append(" REMARK=?, ");		
			sql.append(" UPDATEUSERID=?, ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" PHOTO_1=?,");
			sql.append(" PHOTO_2=?,");
			sql.append(" PHOTO_3=?,");
			sql.append(" PHOTO_4=?,");
			sql.append(" PHOTO_5=?,");
			sql.append(" PHOTO_6=?,");
			sql.append(" PHOTO_7=?,");
			sql.append(" PHOTO_8=?,");
			sql.append(" LOCATIONSERIAL=?, ");
			sql.append(" STATUS=? ");
			sql.append(" WHERE ");
			sql.append(" ID=? ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, dto.getRent());
			ps.setTimestamp(2, dto.getStartDate());
			ps.setTimestamp(3, dto.getEndDate());
			ps.setString(4, dto.getOwnerName());
			ps.setInt(5, dto.getOwnerSex());
			ps.setTimestamp(6, dto.getOwnerBirthday());
			ps.setString(7, dto.getOwnerIdCardNo());
			ps.setString(8, dto.getOwnerPhone());
			ps.setString(9, dto.getOwnerAddress());
			ps.setString(10, dto.getRemark());
			ps.setInt(11, dto.getUpdateUserId());
			ps.setString(12, dto.getPhoto1());
			ps.setString(13, dto.getPhoto2());
			ps.setString(14, dto.getPhoto3());
			ps.setString(15, dto.getPhoto4());
			ps.setString(16, dto.getPhoto5());
			ps.setString(17, dto.getPhoto6());
			ps.setString(18, dto.getPhoto7());
			ps.setString(19, dto.getPhoto8());			
			ps.setString(20, dto.getLocationSerial());
			ps.setInt(21, dto.getStatus());
			ps.setLong(22, dto.getId());
			ps.executeUpdate();
					
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 更新合同状态
	 * */
	public void updateContractStatus(RentalInfoDto dto)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_CONTRACT_INFO);
			sql.append(" SET ");		
			sql.append(" STATUS=?, ");
			sql.append(" UPDATEUSERID=?, ");
			sql.append(" UPDATETIME=SYSDATE() ");
			sql.append(" WHERE ");
			sql.append(" ID=? ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, dto.getStatus());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setLong(3, dto.getId());		
			ps.executeUpdate();
					
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 判断合同编号是否存在
	 * */
	public boolean isExistRentalLocationBySerial(String serial)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL=? ");
		sql.append(" AND STATUS <> 4");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			while(rs.next()){
				flag = true;
			}
			return flag;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		
		} finally {
			super.close(rs, ps);
		}		
	}
	
	public boolean checkLocationSerialUsed(String serial,String locationSerial)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" LOCATIONSERIAL=? ");
		sql.append(" AND SERIAL<>? ");
		sql.append(" AND (STATUS=1 or STATUS=2)");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, locationSerial);
			ps.setString(2, serial);
			rs = ps.executeQuery();
			while(rs.next()){
				flag = true;
			}
			return flag;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		
		} finally {
			super.close(rs, ps);
		}
	}
	
	public List<RentalInfoDto> checkLocationStartDate(String locationSerial)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" SERIAL, ");
		sql.append(" STARTDATE, ");
		sql.append(" ENDDATE ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" LOCATIONSERIAL=? ");
		sql.append(" AND (STATUS=1 or STATUS=2)");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, locationSerial);
			rs = ps.executeQuery();
			RentalInfoDto dto = null;
			while(rs.next()){
	     		dto = new RentalInfoDto();
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setStartDate(rs.getTimestamp("STARTDATE"));
	     		dto.setEndDate(rs.getTimestamp("ENDDATE"));
	     		list.add(dto);
			}
			
			return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		
		} finally {
			super.close(rs, ps);
		}
	}
	
	
	/**
	 * 查询合同状态
	 * */
	public int searchStatusById(long id)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		int status = 0;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" STATUS ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" Id=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				status = rs.getInt("STATUS");
			}
			return status;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		
		} finally {
			super.close(rs, ps);
		}		
	}
	
	/**
	 * 合同信息List查询
	 * */
	public List<RentalInfoDto> searchRentalContractInfo(String serial,String locationSerial,String ownerName,
			String status,String startDateS,String startDateE,String endDateS,String endDateE,int rentalArea) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 0;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" CONTRACT.ID, ");
		sql.append(" CONTRACT.SERIAL, ");
		sql.append(" CONTRACT.LOCATIONSERIAL, ");
		sql.append(" CONTRACT.OWNERNAME, ");
		sql.append(" CONTRACT.OWNERIDCARDNO, ");
		sql.append(" CONTRACT.OWNERPHONE, ");		
		sql.append(" CONTRACT.STARTDATE, ");
		sql.append(" CONTRACT.ENDDATE, ");
		sql.append(" CONTRACT.STATUS, ");
		sql.append(" RENTALLOCATION.AREA ");	
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append("   AS CONTRACT     ");
		sql.append("LEFT JOIN     ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO);
		sql.append("   AS RENTALLOCATION      ");
		sql.append("ON CONTRACT.LOCATIONSERIAL = RENTALLOCATION.SERIAL   ");
		sql.append(" WHERE ");
		if(serial!=null&&!"".equals(serial))
		{
			if(flag == 0){			
				flag=1;
			}
			sql.append(" CONTRACT.SERIAL = '"+serial+"'");//合同编号
		}
		if(locationSerial!=null&&!"".equals(locationSerial))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" CONTRACT.LOCATIONSERIAL = '"+locationSerial+"'");//租位编号
		}
		if(ownerName!=null&&!"".equals(ownerName))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" CONTRACT.OWNERNAME = '"+ownerName+"'");//合同人名字
		}
		if(status!=null&&!"".equals(status))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" STATUS in ("+status+")");//合同状态
		}
		if(!StringUtil.isNullOrBlank(startDateS)&& !StringUtil.isNullOrBlank(startDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(CONTRACT.STARTDATE,'%Y-%m-%d') >=  '"+startDateS+"'");//开始时间
			sql.append(" AND DATE_FORMAT(CONTRACT.STARTDATE,'%Y-%m-%d') <=  '"+startDateE+"'");//开始时间
		}
		
		if(!StringUtil.isNullOrBlank(startDateS) && StringUtil.isNullOrBlank(startDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(CONTRACT.STARTDATE,'%Y-%m-%d') =  '"+startDateS+"'");//开始时间
			
		}
		
		if(StringUtil.isNullOrBlank(startDateS) && !StringUtil.isNullOrBlank(startDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(CONTRACT.STARTDATE,'%Y-%m-%d') =  '"+startDateE+"'");//开始时间
			
		}
		
		if(!StringUtil.isNullOrBlank(endDateS)&& !StringUtil.isNullOrBlank(endDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(CONTRACT.ENDDATE,'%Y-%m-%d') >=  '"+endDateS+"'");//结束时间
			sql.append(" AND DATE_FORMAT(CONTRACT.ENDDATE,'%Y-%m-%d') <=  '"+endDateE+"'");//结束时间
		}
		
		if(!StringUtil.isNullOrBlank(endDateS)&& StringUtil.isNullOrBlank(endDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(CONTRACT.ENDDATE,'%Y-%m-%d') =  '"+endDateS+"'");//结束时间
		}
		
		if(StringUtil.isNullOrBlank(endDateS)&& !StringUtil.isNullOrBlank(endDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(CONTRACT.ENDDATE,'%Y-%m-%d') =  '"+endDateE+"'");//结束时间
		}
		
		if(rentalArea > 0)
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" RENTALLOCATION.AREA = '"+rentalArea+"'");//租位区域
		}

		sql.append(" ORDER BY STARTDATE ASC");
		try {
			ps = conn.prepareStatement(getSql(sql.toString()));

			rs = ps.executeQuery();
			RentalInfoDto dto = null;
			int i=0;
	     	while (rs.next()) {
	     		dto = new RentalInfoDto();
	     		dto.setId(rs.getInt("ID"));
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setLocationSerial(rs.getString("LOCATIONSERIAL"));
	     		dto.setOwnerName(rs.getString("OWNERNAME"));
	     		dto.setOwnerIdCardNo(rs.getString("OWNERIDCARDNO"));
	     		dto.setOwnerPhone(rs.getString("OWNERPHONE"));
	     		dto.setStartDate(rs.getTimestamp("STARTDATE"));
	     		dto.setEndDate(rs.getTimestamp("ENDDATE"));
	     		dto.setStatus(rs.getInt("STATUS"));
	     		dto.setArea(rs.getInt("AREA"));
	     		if(i%2 == 0){
	     			dto.setCssFlag(1);
				}else{
					dto.setCssFlag(2);
				}
	     		int num = (i+1)+(getPageNo()-1)*20;
	     		dto.setRowNo(num);
	     		list.add(dto);
	     		i++;
			}
			return list;	
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	
	/**
	 * 合同信息操作记录List查询
	 * */
	public List<RentalInfoDto> searchRentalContractUpdateHistroy(String serial,String handleName,
			String handleType,String handleDateS,String handleDateE) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 0;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" ID, ");
		sql.append(" SERIAL, ");
		sql.append(" HANDLETYPE, ");
		sql.append(" REMARK, ");
		sql.append(" UPDATEUSERID, ");
		sql.append(" UPDATETIME ");		
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_UPDATE_HISTROY);
		sql.append(" WHERE ");
		if(serial!=null&&!"".equals(serial))
		{
			if(flag == 0){			
				flag=1;
			}
			sql.append(" SERIAL = '"+serial+"'");//合同编号
		}
		if(handleName!=null&&!"".equals(handleName))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" UPDATEUSERID = '"+handleName+"'");//操作人姓名
		}
		if(handleType!=null&&!"".equals(handleType))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" HANDLETYPE in ("+handleType+")");//操作类型
		}
		if(!StringUtil.isNullOrBlank(handleDateS)&& !StringUtil.isNullOrBlank(handleDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(UPDATETIME,'%Y-%m-%d') >=  '"+handleDateS+"'");//开始时间
			sql.append(" AND DATE_FORMAT(UPDATETIME,'%Y-%m-%d') <=  '"+handleDateE+"'");//开始时间
		}
		
		if(!StringUtil.isNullOrBlank(handleDateS) && StringUtil.isNullOrBlank(handleDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(UPDATETIME,'%Y-%m-%d') =  '"+handleDateS+"'");//开始时间
			
		}
		
		if(StringUtil.isNullOrBlank(handleDateS) && !StringUtil.isNullOrBlank(handleDateE))
		{
			if(flag == 0){			
				flag=1;
			}else {
				sql.append(" AND ");
			}
			sql.append(" DATE_FORMAT(UPDATETIME,'%Y-%m-%d') =  '"+handleDateE+"'");//开始时间
			
		}
		
		sql.append(" ORDER BY UPDATETIME DESC");
		try {
			ps = conn.prepareStatement(getSql(sql.toString()));

			rs = ps.executeQuery();
			RentalInfoDto dto = null;
			int i=0;
	     	while (rs.next()) {
	     		dto = new RentalInfoDto();
	     		dto.setId(rs.getInt("ID"));
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setHandleType(rs.getInt("HANDLETYPE"));
	     		dto.setRemark(rs.getString("REMARK"));
	     		dto.setHandleName(StringUtil.getString(rs.getInt("UPDATEUSERID")));
	     		dto.setHandleDate(rs.getTimestamp("UPDATETIME"));
	     		if(i%2 == 0){
	     			dto.setCssFlag(1);
				}else{
					dto.setCssFlag(2);
				}
	     		int num = (i+1)+(getPageNo()-1)*20;
	     		dto.setRowNo(num);
	     		list.add(dto);
	     		i++;
			}
			return list;	
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 合同详细信息查询
	 * */
	public RentalInfoDto searchRentalContractDetailInfo(long id) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		RentalInfoDto dto = new RentalInfoDto();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");	
		sql.append(" CONTRACT.ID, ");		
		sql.append(" CONTRACT.SERIAL, ");			
		sql.append(" CONTRACT.RENT, ");
		sql.append(" CONTRACT.STARTDATE, ");
		sql.append(" CONTRACT.ENDDATE, ");
		sql.append(" CONTRACT.STATUS, ");
		sql.append(" CONTRACT.LOCATIONSERIAL, ");
		sql.append(" CONTRACT.OWNERNAME, ");
		sql.append(" CONTRACT.OWNERSEX, ");
		sql.append(" CONTRACT.OWNERBIRTHDAY, ");
		sql.append(" CONTRACT.OWNERIDCARDNO, ");
		sql.append(" CONTRACT.OWNERPHONE, ");			
		sql.append(" CONTRACT.OWNERADDRESS, ");			
		sql.append(" CONTRACT.REMARK, ");
		sql.append(" CONTRACT.INSERTUSERID,");
		sql.append(" CONTRACT.INSERTTIME, ");			
		sql.append(" CONTRACT.UPDATEUSERID, ");
		sql.append(" CONTRACT.UPDATETIME, ");
		sql.append(" CONTRACT.PHOTO_1, ");
		sql.append(" CONTRACT.PHOTO_2, ");
		sql.append(" CONTRACT.PHOTO_3, ");
		sql.append(" CONTRACT.PHOTO_4, ");
		sql.append(" CONTRACT.PHOTO_5, ");
		sql.append(" CONTRACT.PHOTO_6, ");
		sql.append(" CONTRACT.PHOTO_7, ");
		sql.append(" CONTRACT.PHOTO_8, ");
		sql.append(" RENTALLOCATION.AREA ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" AS CONTRACT ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO );
		sql.append(" AS RENTALLOCATION ");
		sql.append(" ON CONTRACT.LOCATIONSERIAL = RENTALLOCATION.SERIAL  ");
		sql.append(" WHERE ");
		sql.append(" CONTRACT.ID = ? ");

		try {			
			ps = conn.prepareStatement(sql.toString());			
			ps.setLong(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				dto.setId(rs.getLong("ID"));
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setRent(rs.getInt("RENT"));
	     		dto.setStartDate(rs.getTimestamp("STARTDATE"));
	     		dto.setEndDate(rs.getTimestamp("ENDDATE"));
	     		dto.setStatus(rs.getInt("STATUS"));
	     		dto.setLocationSerial(rs.getString("LOCATIONSERIAL"));
	     		dto.setOwnerName(rs.getString("OWNERNAME"));
	     		dto.setOwnerSex(rs.getInt("OWNERSEX"));
	     		dto.setOwnerBirthday(rs.getTimestamp("OWNERBIRTHDAY"));
	     		dto.setOwnerIdCardNo(rs.getString("OWNERIDCARDNO"));
	     		dto.setOwnerPhone(rs.getString("OWNERPHONE"));
	     		dto.setOwnerAddress(rs.getString("OWNERADDRESS"));
	     		dto.setRemark(rs.getString("REMARK"));
	     		dto.setInsertUserId(rs.getInt("INSERTUSERID"));
	     		dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
	     		dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
	     		dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
	     		dto.setPhoto1(rs.getString("PHOTO_1"));
	     		dto.setPhoto2(rs.getString("PHOTO_2"));
	     		dto.setPhoto3(rs.getString("PHOTO_3"));
	     		dto.setPhoto4(rs.getString("PHOTO_4"));
	     		dto.setPhoto5(rs.getString("PHOTO_5"));
	     		dto.setPhoto6(rs.getString("PHOTO_6"));
	     		dto.setPhoto7(rs.getString("PHOTO_7"));
	     		dto.setPhoto8(rs.getString("PHOTO_8"));
	     		dto.setArea(rs.getInt("AREA"));
			}						
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	
	/**
	 * 合同详细信息查询
	 * */
	public RentalInfoDto searchRentalContractDetailInfoBySerial(String serial) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		RentalInfoDto dto = new RentalInfoDto();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");	
		sql.append(" ID, ");		
		sql.append(" SERIAL, ");			
		sql.append(" RENT, ");
		sql.append(" STARTDATE, ");
		sql.append(" ENDDATE, ");
		sql.append(" STATUS, ");
		sql.append(" LOCATIONSERIAL, ");
		sql.append(" OWNERNAME, ");
		sql.append(" OWNERSEX, ");
		sql.append(" OWNERBIRTHDAY, ");
		sql.append(" OWNERIDCARDNO, ");
		sql.append(" OWNERPHONE, ");			
		sql.append(" OWNERADDRESS, ");			
		sql.append(" REMARK, ");
		sql.append(" INSERTUSERID,");
		sql.append(" INSERTTIME, ");			
		sql.append(" UPDATEUSERID, ");
		sql.append(" UPDATETIME, ");
		sql.append(" PHOTO_1, ");
		sql.append(" PHOTO_2, ");
		sql.append(" PHOTO_3, ");
		sql.append(" PHOTO_4, ");
		sql.append(" PHOTO_5, ");
		sql.append(" PHOTO_6, ");
		sql.append(" PHOTO_7, ");
		sql.append(" PHOTO_8 ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" SERIAL = ? ");

		try {			
			ps = conn.prepareStatement(sql.toString());			
			ps.setString(1, serial);
			rs = ps.executeQuery();
			while (rs.next()) {
				dto.setId(rs.getLong("ID"));
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setRent(rs.getInt("RENT"));
	     		dto.setStartDate(rs.getTimestamp("STARTDATE"));
	     		dto.setEndDate(rs.getTimestamp("ENDDATE"));
	     		dto.setStatus(rs.getInt("STATUS"));
	     		dto.setLocationSerial(rs.getString("LOCATIONSERIAL"));
	     		dto.setOwnerName(rs.getString("OWNERNAME"));
	     		dto.setOwnerSex(rs.getInt("OWNERSEX"));
	     		dto.setOwnerBirthday(rs.getTimestamp("OWNERBIRTHDAY"));
	     		dto.setOwnerIdCardNo(rs.getString("OWNERIDCARDNO"));
	     		dto.setOwnerPhone(rs.getString("OWNERPHONE"));
	     		dto.setOwnerAddress(rs.getString("OWNERADDRESS"));
	     		dto.setRemark(rs.getString("REMARK"));
	     		dto.setInsertUserId(rs.getInt("INSERTUSERID"));
	     		dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
	     		dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
	     		dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
	     		dto.setPhoto1(rs.getString("PHOTO_1"));
	     		dto.setPhoto2(rs.getString("PHOTO_2"));
	     		dto.setPhoto3(rs.getString("PHOTO_3"));
	     		dto.setPhoto4(rs.getString("PHOTO_4"));
	     		dto.setPhoto5(rs.getString("PHOTO_5"));
	     		dto.setPhoto6(rs.getString("PHOTO_6"));
	     		dto.setPhoto7(rs.getString("PHOTO_7"));
	     		dto.setPhoto8(rs.getString("PHOTO_8"));	     		
			}						
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	
	/**
	 * 租位合同查询
	 * */
	public List<RentalInfoDto> searchRentalContractInfoByLocationSerial(String locationSerial) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		RentalInfoDto dto = new RentalInfoDto();
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");	
		sql.append(" ID, ");		
		sql.append(" SERIAL, ");			
		sql.append(" RENT, ");
		sql.append(" STARTDATE, ");
		sql.append(" ENDDATE, ");
		sql.append(" STATUS, ");
		sql.append(" LOCATIONSERIAL, ");
		sql.append(" OWNERNAME, ");
		sql.append(" OWNERSEX, ");
		sql.append(" OWNERBIRTHDAY, ");
		sql.append(" OWNERIDCARDNO, ");
		sql.append(" OWNERPHONE, ");			
		sql.append(" OWNERADDRESS, ");			
		sql.append(" REMARK, ");
		sql.append(" INSERTUSERID,");
		sql.append(" INSERTTIME, ");			
		sql.append(" UPDATEUSERID, ");
		sql.append(" UPDATETIME, ");
		sql.append(" PHOTO_1, ");
		sql.append(" PHOTO_2, ");
		sql.append(" PHOTO_3, ");
		sql.append(" PHOTO_4, ");
		sql.append(" PHOTO_5, ");
		sql.append(" PHOTO_6, ");
		sql.append(" PHOTO_7, ");
		sql.append(" PHOTO_8 ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE ");
		sql.append(" LOCATIONSERIAL = ? ");
		sql.append(" AND STATUS IN (1,2) ");
		sql.append(" ORDER BY STARTDATE ASC");
		
		try {			
			ps = conn.prepareStatement(sql.toString());			
			ps.setString(1, locationSerial);
			rs = ps.executeQuery();
			while (rs.next()) {
				dto.setId(rs.getLong("ID"));
	     		dto.setSerial(rs.getString("SERIAL"));
	     		dto.setRent(rs.getInt("RENT"));
	     		dto.setStartDate(rs.getTimestamp("STARTDATE"));
	     		dto.setEndDate(rs.getTimestamp("ENDDATE"));
	     		dto.setStatus(rs.getInt("STATUS"));
	     		dto.setLocationSerial(rs.getString("LOCATIONSERIAL"));
	     		dto.setOwnerName(rs.getString("OWNERNAME"));
	     		dto.setOwnerSex(rs.getInt("OWNERSEX"));
	     		dto.setOwnerBirthday(rs.getTimestamp("OWNERBIRTHDAY"));
	     		dto.setOwnerIdCardNo(rs.getString("OWNERIDCARDNO"));
	     		dto.setOwnerPhone(rs.getString("OWNERPHONE"));
	     		dto.setOwnerAddress(rs.getString("OWNERADDRESS"));
	     		dto.setRemark(rs.getString("REMARK"));
	     		dto.setInsertUserId(rs.getInt("INSERTUSERID"));
	     		dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
	     		dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
	     		dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
	     		dto.setPhoto1(rs.getString("PHOTO_1"));
	     		dto.setPhoto2(rs.getString("PHOTO_2"));
	     		dto.setPhoto3(rs.getString("PHOTO_3"));
	     		dto.setPhoto4(rs.getString("PHOTO_4"));
	     		dto.setPhoto5(rs.getString("PHOTO_5"));
	     		dto.setPhoto6(rs.getString("PHOTO_6"));
	     		dto.setPhoto7(rs.getString("PHOTO_7"));
	     		dto.setPhoto8(rs.getString("PHOTO_8"));	
	     		list.add(dto);
			}						
			return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	/**
	 * 模糊查询合同编号列表
	 * @return
	 * @throws TmsDaoException
	 */
	public List<RentalInfoDto> findSerialList(String serial)throws
	TmsDaoException{

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE SERIAL like '%"); 	
		sql.append(serial); 
		sql.append("%'");
		sql.append("AND STATUS <> 16");
		sql.append(" ORDER BY SERIAL ASC LIMIT 30");
		
		RentalInfoDto rentalInfoDto = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				rentalInfoDto = new RentalInfoDto();
				rentalInfoDto.setId(rs.getLong("ID"));
				rentalInfoDto.setSerial(rs.getString("SERIAL"));
				list.add(rentalInfoDto);
			}
			return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	/**
	 * 模糊查询合同编号列表
	 * @return
	 * @throws TmsDaoException
	 */
	public List<RentalInfoDto> findSerialInfoList(String serial)throws
			TmsDaoException{

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("SERIAL ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE SERIAL like '%"); 	
		sql.append(serial); 
		sql.append("%'");
		sql.append("AND (STATUS = 1 OR STATUS = 2)");
		sql.append(" ORDER BY SERIAL ASC");
		
		RentalInfoDto rentalInfoDto = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {

				rentalInfoDto = new RentalInfoDto();
				rentalInfoDto.setId(rs.getLong("ID"));
				rentalInfoDto.setSerial(rs.getString("SERIAL"));

				list.add(rentalInfoDto);
			}
			
			return list;

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}

	/**
	 * 模糊查询合同人姓名列表
	 * @return
	 * @throws TmsDaoException
	 */
	public List<RentalInfoDto> findOwnerNameList(String ownerName)throws
	TmsDaoException{

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("OWNERNAME ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO);
		sql.append(" WHERE OWNERNAME like '%"); 	
		sql.append(ownerName); 
		sql.append("%'");
		sql.append(" ORDER BY OWNERNAME ASC");
		
		RentalInfoDto rentalInfoDto = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {

				rentalInfoDto = new RentalInfoDto();
				rentalInfoDto.setId(rs.getLong("ID"));
				rentalInfoDto.setOwnerName(rs.getString("OWNERNAME"));

				list.add(rentalInfoDto);
			}
			
			return list;

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	

	/**
	 * 模糊查询操作人名列表
	 * @return
	 * @throws TmsDaoException
	 */
	public List<RentalInfoDto> findHandleNameList(String realName)throws
	TmsDaoException{

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RentalInfoDto> list = new ArrayList<RentalInfoDto>();

		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("REALNAME ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" WHERE REALNAME like '%"); 	
		sql.append(realName); 
		sql.append("%'");
		sql.append(" ORDER BY REALNAME ASC");
		
		RentalInfoDto rentalInfoDto = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {

				rentalInfoDto = new RentalInfoDto();
				rentalInfoDto.setId(rs.getLong("ID"));
				rentalInfoDto.setHandleName(rs.getString("REALNAME"));

				list.add(rentalInfoDto);
			}
			
			return list;

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	/**
	 * 合同租位信息查询
	 * @return
	 * @throws TmsDaoException
	 */
	public RentalInfoDto findContractInfoBySerial(String serial)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		RentalInfoDto dto = new RentalInfoDto();

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("CONTRACT.SERIAL AS SERIAL, ");
		sql.append("CONTRACT.LOCATIONSERIAL AS LOCATIONSERIAL, ");
		sql.append("CONTRACT.STARTDATE AS STARTDATE, ");
		sql.append("CONTRACT.ENDDATE AS ENDDATE, ");
		sql.append("RENTALLOCATION.TYPE AS TYPE, ");
		sql.append("RENTALLOCATION.ACREAGE AS ACREAGE, ");
		sql.append("RENTALLOCATION.CARPORT AS CARPORT, ");
		sql.append("RENTALLOCATION.AREA AS AREA, ");
		sql.append("RENTALLOCATION.ADDRESS AS ADDRESS, ");
		sql.append("RENTALLOCATION.ANNUALTRANSACTIONVOLUME AS ANNUALTRANSACTIONVOLUME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_CONTRACT_INFO );
		sql.append(" AS CONTRACT ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_RENTALLOCATION_INFO );
		sql.append(" AS RENTALLOCATION ");
		sql.append(" ON CONTRACT.LOCATIONSERIAL = RENTALLOCATION.SERIAL  ");
		sql.append("WHERE ");
		sql.append("CONTRACT.SERIAL = ?");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			while (rs.next()) {
				dto.setSerial(rs.getString("SERIAL"));
				dto.setLocationSerial(rs.getString("LOCATIONSERIAL"));
				dto.setStartDate(rs.getTimestamp("STARTDATE"));
				dto.setEndDate(rs.getTimestamp("ENDDATE"));
				dto.setRentalType(rs.getInt("TYPE"));
				dto.setAcreage(rs.getInt("ACREAGE"));
				dto.setArea(rs.getInt("AREA"));
				dto.setCarPort(rs.getInt("CARPORT"));
				dto.setAddress(rs.getString("ADDRESS"));	
				dto.setAnnualTransactionVolume(rs.getString("ANNUALTRANSACTIONVOLUME"));
			}
			return dto;
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	
	/**
	 * 插入合同信息
	 * */
	public void insertContractUpdateHistroy(RentalInfoDto dto)
			throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_CONTRACT_UPDATE_HISTROY);
			sql.append("(");		
			sql.append(" SERIAL, ");			
			sql.append(" HANDLETYPE, ");
			sql.append(" REMARK, ");
			sql.append(" UPDATEUSERID, ");
			sql.append(" UPDATETIME ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,SYSDATE())");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getHandleType());
			ps.setString(3, dto.getRemark());
			ps.setInt(4, dto.getUpdateUserId());
			
			ps.executeUpdate();
					
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
}
